﻿/*
#**************************************************************************
#  openSIS is a student information system for public and non-public 
#  schools from Open Solutions for Education, Inc. web: www.os4ed.com
#
#  openSIS is  web-based, open source, and comes packed with features that 
#  include student demographic info, scheduling, grade book, attendance, 
#  report cards, eligibility, transcripts, parent portal, 
#  student portal and more.   
#
#  Visit the openSIS web site at http://www.opensis.com to learn more.
#  If you have question regarding this system or the license, please send 
#  an email to info@os4ed.com.
#
#  This program is released under the terms of the GNU General Public License  
#  as  published by the Free Software Foundation, version 2 of the License. 
#  See license.txt.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.
#
#  You should have received a copy of the GNU General Public License
#  along with this program.  If not, see <http://www.gnu.org/licenses/>.
#
#**************************************************************************** 

*/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OpenSISDataConnection;
using OSISStudentSetupBusinessLayer;
using System.Data;
using System.Globalization;

namespace OSISStudentSetupDataLayer
{
    public class StudentSetupDL
    {
        public StudentSetupDL()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public static string server = System.Configuration.ConfigurationManager.AppSettings["appserver"];

        public void controladd(StudentSetupBL studentsetup)
        {
            String StudentSetupExtrafield = "INSERT into Student_Setup_Custom_Field(extrafield_id,field_name,field_type_id,sch_id,st_id,staff_type_id,Category_Id) values(" + studentsetup.ExtraFieldId + ",N'" + studentsetup.FieldName + "'," + studentsetup.ControlType + "," + studentsetup.SchoolId + "," + studentsetup.StudentId + "," + studentsetup.StaffTypeId + "," + studentsetup.CategoryId + ")";
            DataConnection.ConnectionPath.ExecuteCommand(StudentSetupExtrafield);
        }

        public void addddllistItem(StudentSetupBL studentsetup)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("exec getMax ListItem_Id,Custom_Field_List_Item");
                String DdlListItem = "INSERT into Custom_Field_List_Item(ListItem_Id,control_id,ListItem_name) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + studentsetup.ExtraFieldId + ",N'" + studentsetup.DdlListitemName + "')";
                DataConnection.ConnectionPath.ExecuteCommand(DdlListItem);
            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('ListItem_Id','Custom_Field_List_Item')");
                String DdlListItem = "INSERT into Custom_Field_List_Item(ListItem_Id,control_id,ListItem_name) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + studentsetup.ExtraFieldId + ",'" + studentsetup.DdlListitemName + "')";
                DataConnection.ConnectionPath.ExecuteCommand(DdlListItem);
            }
        }

        public System.Data.DataSet GetExtraFieldId(StudentSetupBL extrafldid)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("exec getMax extrafield_id,Student_Setup_Custom_Field");
                return ds;
            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('extrafield_id','Student_Setup_Custom_Field')");
                return ds;
            }
            else
            {
                DataSet ds = null;
                return ds;
            }
        }

        public void deletelistitems(StudentSetupBL extrafldid)
        {
            String deletelistitems = "delete from Custom_Field_List_Item where control_id=" + extrafldid.ExtraFieldId + "";
            DataConnection.ConnectionPath.ExecuteCommand(deletelistitems);
        }

        public void DeleteCustomField(StudentSetupBL extrafldid)
        {
            String deletecustomfields = "delete from Student_Setup_Custom_Field where extrafield_id=" + extrafldid.ExtraFieldId + "";
            DataConnection.ConnectionPath.ExecuteCommand(deletecustomfields);
        }

        public DataSet PopulateUserCtrls()
        {
            DataSet ds = null;
            ds = DataConnection.ConnectionPath.Getdata("select * from studentsetup_controls");
            return ds;
        }

        public System.Data.DataTable PopulatrCustomField(StudentSetupBL studentcontrols)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select * from Student_Setup_Custom_Field where sch_id=" + studentcontrols.SchoolId + " and st_id=" + studentcontrols.StudentId + " ").Tables[0];
            return dt;
        }

        public DataSet PopulateCategory(StudentSetupBL StudentAdd)
        {
            string catagorylist;
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                catagorylist = DataConnection.ConnectionPath.Getdata("select Cat_Id from Student_Setup_School_Specific_Category where Sch_Id=" + StudentAdd.SchoolId + "").Tables[0].Rows[0][0].ToString();
                ds = DataConnection.ConnectionPath.Getdata("select SUBSTRING(Cat_Name,1,(len(Cat_Name)-24)) AS 'Cat_Name',Cat_Id from Student_Setup_Category where Cat_Id in (" + catagorylist + ")");
                return ds;
            }
            if (server == "MySQL")
            {
                catagorylist = DataConnection.ConnectionPath.Getdata("select Cat_Id from Student_Setup_School_Specific_Category where Sch_Id=" + StudentAdd.SchoolId + "").Tables[0].Rows[0][0].ToString();
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("select SUBSTRING(Cat_Name,1,(LENGTH(Cat_Name)-24)) AS 'Cat_Name',Cat_Id from Student_Setup_Category where Cat_Id in (" + catagorylist + ")");
                return ds;
            }
            else
            {
                DataSet ds = null;
                return ds;
            }
        }

        public void addcategory(StudentSetupBL addcategory)
        {
            DataSet dsSO = DataConnection.ConnectionPath.Getdata("select max(Short_Order + 1)from dbo.Student_Setup_Category");
            try
            {
                if (server == "MSSQLServer")
                {
                    DataSet ds = null;
                    object Cat_Id = "";
                    object List_Cat_Id = "";

                    

                    ds = DataConnection.ConnectionPath.Getdata("exec getMax Cat_Id,Student_Setup_Category");
                    String categoryadd = "INSERT into Student_Setup_Category(Cat_Id,Cat_Name,Short_Order) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + addcategory.CategoryName + "'," + dsSO.Tables[0].Rows[0][0] + ")";
                    DataConnection.ConnectionPath.ExecuteCommand(categoryadd);
                    Cat_Id = DataConnection.ConnectionPath.Getdata("select MAX(Cat_Id) AS MaxCatId from Student_Setup_Category").Tables[0].Rows[0][0];
                    List_Cat_Id = DataConnection.ConnectionPath.Getdata("select Cat_Id from Student_Setup_School_Specific_Category where Sch_Id=" + addcategory.SchoolId + "").Tables[0].Rows[0][0];
                    string Concate_categoty = List_Cat_Id + "," + Cat_Id;
                    String updateCategoryList = "update Student_Setup_School_Specific_Category set Cat_Id='" + Concate_categoty + "' where Sch_Id =" + addcategory.SchoolId + "";
                    DataConnection.ConnectionPath.ExecuteCommand(updateCategoryList);
                }
                if (server == "MySQL")
                {
                    DataSet ds = null;
                    object Cat_Id = "";
                    object List_Cat_Id = "";
                    ds = DataConnection.ConnectionPath.Getdata("Call getMax ('Cat_Id','Student_Setup_Category')");
                    String categoryadd = "INSERT into Student_Setup_Category(Cat_Id,Cat_Name,Short_Order) values(" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + ",N'" + addcategory.CategoryName + "'," + dsSO.Tables[0].Rows[0][0] + ")";
                    DataConnection.ConnectionPath.ExecuteCommand(categoryadd);
                    Cat_Id = DataConnection.ConnectionPath.Getdata("select MAX(Cat_Id) AS MaxCatId from Student_Setup_Category").Tables[0].Rows[0][0];
                    List_Cat_Id = DataConnection.ConnectionPath.Getdata("select Cat_Id from Student_Setup_School_Specific_Category where Sch_Id=" + addcategory.SchoolId + "").Tables[0].Rows[0][0];
                    string Concate_categoty = List_Cat_Id + "," + Cat_Id;
                    String updateCategoryList = "update Student_Setup_School_Specific_Category set Cat_Id='" + Concate_categoty + "' where Sch_Id =" + addcategory.SchoolId + "";
                    DataConnection.ConnectionPath.ExecuteCommand(updateCategoryList);
                }
            }
            catch
            {
            }
        }

        public System.Data.DataTable getmaxcatergoryid(StudentSetupBL studentcontrols)
        {
            DataTable dt = null;
            dt = DataConnection.ConnectionPath.Getdata("select MAX(Cat_Id) AS MaxCatId from Student_Setup_Category").Tables[0];
            return dt;
        }

        public System.Data.DataTable getschoolspecificcategory(StudentSetupBL SchoolSpCat)
        {
            if (server == "MSSQLServer")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("getschoolspecificcategory " + SchoolSpCat.SchoolId + "").Tables[0];
                return dt;
            }
            if (server == "MySQL")
            {
                DataTable dt = null;
                dt = DataConnection.ConnectionPath.Getdata("Call getschoolspecificcategory (" + SchoolSpCat.SchoolId + ")").Tables[0];
                return dt;
            }
            else
            {
                DataTable dt = null;
                return dt;
            }
        }

        public System.Data.DataTable getfieldsbycategory(StudentSetupBL SchoolSpCat)
        {
            if (server == "MSSQLServer")
            {
                DataTable dtdefault = null;
                dtdefault = DataConnection.ConnectionPath.Getdata("getfieldsbycategory " + SchoolSpCat.CategoryId + "," + SchoolSpCat.SchoolId + "").Tables[0];
                return dtdefault;
            }
            if (server == "MySQL")
            {
                DataTable dtdefault = null;
                dtdefault = DataConnection.ConnectionPath.Getdata("Call getfieldsbycategory (" + SchoolSpCat.CategoryId + "," + SchoolSpCat.SchoolId + ")").Tables[0];
                return dtdefault;
            }
            else
            {
                DataTable dt = null;
                return dt;
            }
        }

        public void updatedefaultfieldstatus(StudentSetupBL fieldstatus)
        {
            if (server == "MSSQLServer")
            {
                String updatestatus = "updatedefaultfieldstatus '" + fieldstatus.FieldStatus + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + "";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
            if (server == "MySQL")
            {
                String updatestatus = "Call updatedefaultfieldstatus ('" + fieldstatus.FieldStatus + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + ")";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
            else
            {
            }
        }

        public void updatecustomfieldstatus(StudentSetupBL fieldstatus)
        {
            if (server == "MSSQLServer")
            {
                String updatestatus = "updatecustomfieldstatus '" + fieldstatus.FieldStatus + "'," + fieldstatus.FieldId + "";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
            if (server == "MySQL")
            {
                String updatestatus = "Call updatecustomfieldstatus ('" + fieldstatus.FieldStatus + "'," + fieldstatus.FieldId + ")";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
        }

        public void insertdefaultfieldstatus(StudentSetupBL insertstatus)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("exec getMax Status_Id,Student_Setup_Default_Field_Status");
                String insertfieldstatus = "insertdefaultfieldstatus " + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + insertstatus.FieldId + ",'" + insertstatus.FieldStatus + "'," + insertstatus.SchoolId + ",'" + insertstatus.FieldView + "'";
                DataConnection.ConnectionPath.ExecuteCommand(insertfieldstatus);
            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('Status_Id','Student_Setup_Default_Field_Status')");
                String insertfieldstatus = "Call insertdefaultfieldstatus (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + insertstatus.FieldId + ",'" + insertstatus.FieldStatus + "'," + insertstatus.SchoolId + ",'" + insertstatus.FieldView + "')";
                DataConnection.ConnectionPath.ExecuteCommand(insertfieldstatus);
            }
        }

        public int checkdefaultfieldcount(StudentSetupBL fieldcount)
        {
            if (server == "MSSQLServer")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("checkdefaultfieldcount " + fieldcount.FieldId + "," + fieldcount.SchoolId + "").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            if (server == "MySQL")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("Call checkdefaultfieldcount (" + fieldcount.FieldId + "," + fieldcount.SchoolId + ")").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            else
            {
                return 0;
            }
        }

        public int checkdefaultaddressfieldcount(StudentSetupBL fieldcount)
        {
            if (server == "MSSQLServer")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("checkdefaultaddressfieldcount " + fieldcount.FieldId + "").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            if (server == "MySQL")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("Call checkdefaultaddressfieldcount (" + fieldcount.FieldId + ")").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            else
            {
                return 0;
            }
        }

        public System.Data.DataTable getaddressfieldsbyfieldid(StudentSetupBL AddressFields)
        {
            if (server == "MSSQLServer")
            {
                DataTable dtdefault = null;
                dtdefault = DataConnection.ConnectionPath.Getdata("getaddressfieldsbyfieldid " + AddressFields.FieldId + "," + AddressFields.SchoolId + "").Tables[0];
                return dtdefault;
            }
            if (server == "MySQL")
            {
                DataTable dtdefault = null;
                dtdefault = DataConnection.ConnectionPath.Getdata("Call getaddressfieldsbyfieldid (" + AddressFields.FieldId + "," + AddressFields.SchoolId + ")").Tables[0];
                return dtdefault;
            }
            else
            {
                DataTable dt = null;
                return dt;
            }
        }

        public int checkaddressfieldfromstatustable(StudentSetupBL fieldcount)
        {
            if (server == "MSSQLServer")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("checkaddressfieldfromstatustable " + fieldcount.FieldId + "," + fieldcount.SchoolId + "").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            if (server == "MySQL")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("Call checkaddressfieldfromstatustable (" + fieldcount.FieldId + "," + fieldcount.SchoolId + ")").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            else
            {
                return 0;
            }
        }

        public void updatedefaultaddressfieldstatus(StudentSetupBL fieldstatus)
        {

            if (server == "MSSQLServer")
            {
                String updatestatus = "updatedefaultaddressfieldstatus '" + fieldstatus.FieldStatus + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + "";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
            if (server == "MySQL")
            {
                String updatestatus = "Call updatedefaultaddressfieldstatus ('" + fieldstatus.FieldStatus + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + ")";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
        }

        public void insertdefaultaddressfieldstatus(StudentSetupBL insertstatus)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("exec getMax Status_Id,Student_Setup_Address_Default_Field_Status");
                String insertfieldstatus = "insertdefaultaddressfieldstatus " + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + insertstatus.FieldId + ",'" + insertstatus.FieldStatus + "'," + insertstatus.SchoolId + ",'" + insertstatus.FieldView + "'";
                DataConnection.ConnectionPath.ExecuteCommand(insertfieldstatus);
            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('Status_Id','Student_Setup_Address_Default_Field_Status')");
                String insertfieldstatus = "Call insertdefaultaddressfieldstatus (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + insertstatus.FieldId + ",'" + insertstatus.FieldStatus + "'," + insertstatus.SchoolId + ",'" + insertstatus.FieldView + "')";
                DataConnection.ConnectionPath.ExecuteCommand(insertfieldstatus);
            }
        }

        public int checkadditionalcontactlist(StudentSetupBL fieldcount)
        {
            if (server == "MSSQLServer")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("checkadditionalcontactlist " + fieldcount.FieldId + "," + fieldcount.SchoolId + "").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            if (server == "MySQL")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("Call checkadditionalcontactlist (" + fieldcount.FieldId + "," + fieldcount.SchoolId + ")").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            else
            {
                return 0;
            }
        }

        public System.Data.DataTable getadditionalcontactfields(StudentSetupBL AddressFields)
        {
            if (server == "MSSQLServer")
            {
                DataTable dtcustom = null;
                dtcustom = DataConnection.ConnectionPath.Getdata("getadditionalcontactfields " + AddressFields.FieldId + "," + AddressFields.SchoolId + "").Tables[0];
                return dtcustom;
            }
            if (server == "MySQL")
            {
                DataTable dtcustom = null;
                dtcustom = DataConnection.ConnectionPath.Getdata("Call getadditionalcontactfields (" + AddressFields.FieldId + "," + AddressFields.SchoolId + ")").Tables[0];
                return dtcustom;
            }
            else
            {
                DataTable dt = null;
                return dt;
            }
        }

        public void updateadditionaladdressfieldstatus(StudentSetupBL fieldstatus)
        {
            if (server == "MSSQLServer")
            {
                String updatestatus = "updateadditionaladdressfieldstatus  '" + fieldstatus.FieldStatus + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + "";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
            if (server == "MySQL")
            {
                String updatestatus = "Call updateadditionaladdressfieldstatus  ('" + fieldstatus.FieldStatus + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + ")";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
        }

        public int checkadditionaladdressfieldfromstatustable(StudentSetupBL fieldcount)
        {
            if (server == "MSSQLServer")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("checkadditionaladdressfieldfromstatustable " + fieldcount.FieldId + "," + fieldcount.SchoolId + "").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            if (server == "MySQL")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("Call checkadditionaladdressfieldfromstatustable (" + fieldcount.FieldId + "," + fieldcount.SchoolId + ")").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            else
            {
                return 0;
            }
        }

        public void insertadditionaladdressfieldstatus(StudentSetupBL insertstatus)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("exec getMax Status_Id,Student_Setup_Additional_Contact_Status");
                String insertfieldstatus = "insertadditionaladdressfieldstatus " + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + insertstatus.FieldId + ",'" + insertstatus.FieldStatus + "'," + insertstatus.SchoolId + ",'" + insertstatus.FieldView + "'";
                DataConnection.ConnectionPath.ExecuteCommand(insertfieldstatus);
            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('Status_Id','Student_Setup_Additional_Contact_Status')");
                String insertfieldstatus = "Call insertadditionaladdressfieldstatus (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + insertstatus.FieldId + ",'" + insertstatus.FieldStatus + "'," + insertstatus.SchoolId + ",'" + insertstatus.FieldView + "')";
                DataConnection.ConnectionPath.ExecuteCommand(insertfieldstatus);
            }
        }

        public void updatedefaultfieldview(StudentSetupBL fieldstatus)
        {
            if (server == "MSSQLServer")
            {
                String updatefieldview = "updatedefaultfieldview '" + fieldstatus.FieldView + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + "";
                DataConnection.ConnectionPath.ExecuteCommand(updatefieldview);
            }
            if (server == "MySQL")
            {
                String updatefieldview = "Call updatedefaultfieldview ('" + fieldstatus.FieldView + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + ")";
                DataConnection.ConnectionPath.ExecuteCommand(updatefieldview);
            }
        }

        public void updatecustomfieldview(StudentSetupBL fieldstatus)
        {
            if (server == "MSSQLServer")
            {
                String updatestatus = "updatecustomfieldview '" + fieldstatus.FieldView + "'," + fieldstatus.FieldId + "";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
            if (server == "MySQL")
            {
                String updatestatus = "Call updatecustomfieldview ('" + fieldstatus.FieldView + "'," + fieldstatus.FieldId + ")";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
        }

        public void updatedefaultaddressfieldview(StudentSetupBL fieldstatus)
        {
            if (server == "MSSQLServer")
            {
                String updatestatus = "updatedefaultaddressfieldview '" + fieldstatus.FieldView + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + "";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
            if (server == "MySQL")
            {
                String updatestatus = "Call updatedefaultaddressfieldview ('" + fieldstatus.FieldView + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + ")";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
        }

        public void updateadditionaladdressfieldview(StudentSetupBL fieldstatus)
        {
            if (server == "MSSQLServer")
            {
                String updatestatus = "updateadditionaladdressfieldview '" + fieldstatus.FieldView + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + "";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
            if (server == "MySQL")
            {
                String updatestatus = "Call updateadditionaladdressfieldview ('" + fieldstatus.FieldView + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + ")";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
        }

        public System.Data.DataTable GetAdditionalContact(StudentSetupBL StuSetBL)
        {
            if (server == "MSSQLServer")
            {
                DataTable dtadd = null;
                String getadditonal = "select SSADCF.Field_Type as FieldType,SSADCF.Cont_Id as FieldId,SSADCF.Field_Name as FieldName,SSADCF.Status as IsMandatory, SSACS.* from Student_Setup_Additional_Default_Contact_Field as SSADCF left outer join  (select Cont_ID,Emergency ,Field_View as Field_View,Status from Student_Setup_Additional_Contact_Status where Sch_Id = " + StuSetBL.SchoolId + " )SSACS on SSADCF.Cont_Id = SSACS.Cont_Id order by SSADCF.Cont_Id";
                dtadd = DataConnection.ConnectionPath.Getdata(getadditonal).Tables[0];
                return dtadd;
            }
            if (server == "MySQL")
            {
                DataTable dtadd = null;
                String getadditonal = "select SSADCF.Field_Type as FieldType,SSADCF.Cont_Id as FieldId,SSADCF.Field_Name as FieldName,SSADCF.Status as IsMandatory, SSACS.* from Student_Setup_Additional_Default_Contact_Field as SSADCF left outer join  (select Cont_ID,Emergency ,Field_View as Field_View,Status from Student_Setup_Additional_Contact_Status where Sch_Id = " + StuSetBL.SchoolId + " )SSACS on SSADCF.Cont_Id = SSACS.Cont_Id order by SSADCF.Cont_Id";
                dtadd = DataConnection.ConnectionPath.Getdata(getadditonal).Tables[0];
                return dtadd;
            }
            else
            {
                DataTable dt = null;
                return dt;
            }
        }

        public System.Data.DataTable GetSpecialEducation(StudentSetupBL StuSetBL)
        {
            if (server == "MSSQLServer")
            {
                DataTable dtadd = null;
                String getspled = "select SSPDSE.Def_Field_Id,SSPDSE.Field_Type as FieldType,SSPDSE.Spl_Id as FieldId,SSPDSE.Field_Name as FieldName,SSPDSE.Status as IsMandatory, SSPDSES.* from Student_SetUp_Programs_Default_Special_Education as SSPDSE left outer join  (select Spl_Id,Spl_Field_View as Field_View,Status from Student_SetUp_Programs_Default_Special_Education_Status where sch_id = " + StuSetBL.SchoolId + " )SSPDSES on SSPDSE.Spl_Id = SSPDSES.Spl_Id order by SSPDSE.Spl_Id ";
                dtadd = DataConnection.ConnectionPath.Getdata(getspled).Tables[0];
                return dtadd;
            }
            if (server == "MySQL")
            {
                DataTable dtadd = null;
                String getspled = "select SSPDSE.Def_Field_Id,SSPDSE.Field_Type as FieldType,SSPDSE.Spl_Id as FieldId,SSPDSE.Field_Name as FieldName,SSPDSE.Status as IsMandatory, SSPDSES.* from Student_SetUp_Programs_Default_Special_Education as SSPDSE left outer join  (select Spl_Id,Spl_Field_View as Field_View,Status from Student_SetUp_Programs_Default_Special_Education_Status where sch_id = " + StuSetBL.SchoolId + " )SSPDSES on SSPDSE.Spl_Id = SSPDSES.Spl_Id order by SSPDSE.Spl_Id";
                dtadd = DataConnection.ConnectionPath.Getdata(getspled).Tables[0];
                return dtadd;
            }
            else
            {
                DataTable dt = null;
                return dt;
            }
        }

        public System.Data.DataTable GetGiftedEducation(StudentSetupBL StuSetBL)
        {
            if (server == "MSSQLServer")
            {
                DataTable dtadd = null;
                String getspled = "select SSPDSE.Def_Field_Id,SSPDSE.Field_Type as FieldType,SSPDSE.Gft_Id as FieldId,SSPDSE.Field_Name as FieldName,SSPDSE.Status as IsMandatory, SSPDSES.* from Student_Setup_Programs_Gifted_Default_Field as SSPDSE left outer join  (select Gft_Id,Gft_Field_View as Field_View,Status from Student_Setup_Programs_Gifted_Default_Field_Status where Sch_Id = " + StuSetBL.SchoolId + " )SSPDSES on SSPDSE.Gft_Id = SSPDSES.Gft_Id order by SSPDSE.Gft_Id ";
                dtadd = DataConnection.ConnectionPath.Getdata(getspled).Tables[0];
                return dtadd;
            }
            if (server == "MySQL")
            {
                DataTable dtadd = null;
                String getspled = "select SSPDSE.Def_Field_Id,SSPDSE.Field_Type as FieldType,SSPDSE.Gft_Id as FieldId,SSPDSE.Field_Name as FieldName,SSPDSE.Status as IsMandatory, SSPDSES.* from Student_Setup_Programs_Gifted_Default_Field as SSPDSE left outer join  (select Gft_Id,Gft_Field_View as Field_View,Status from Student_Setup_Programs_Gifted_Default_Field_Status where Sch_Id = " + StuSetBL.SchoolId + " )SSPDSES on SSPDSE.Gft_Id = SSPDSES.Gft_Id order by SSPDSE.Gft_Id ";
                dtadd = DataConnection.ConnectionPath.Getdata(getspled).Tables[0];
                return dtadd;
            }
            else
            {
                DataTable dt = null;
                return dt;
            }
        }

        public int CheckSpecialFieldFromStatusTable(StudentSetupBL fieldcount)
        {
            if (server == "MSSQLServer")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("checkspecialfieldfromstatustable " + fieldcount.FieldId + "," + fieldcount.SchoolId + "").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            if (server == "MySQL")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("Call checkspecialfieldfromstatustable (" + fieldcount.FieldId + "," + fieldcount.SchoolId + ")").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            else
            {
                return 0;
            }
        }

        public void UpdateDefaultSpecialFieldStatus(StudentSetupBL fieldstatus)
        {
            if (server == "MSSQLServer")
            {
                String updatestatus = "updatespecialdefaultfieldviewstatus '" + fieldstatus.FieldStatus + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + "";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
            if (server == "MySQL")
            {
                String updatestatus = "Call updatespecialdefaultfieldviewstatus ('" + fieldstatus.FieldStatus + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + ")";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
        }

        public void UpdateDefaultSpecialFieldView(StudentSetupBL fieldstatus)
        {
            if (server == "MSSQLServer")
            {
                String updatestatus = "updatespecialdefaultfieldview '" + fieldstatus.FieldView + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + "";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
            if (server == "MySQL")
            {
                String updatestatus = "Call updatespecialdefaultfieldview ('" + fieldstatus.FieldView + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + ")";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
        }

        public void insertspecialfieldstatus(StudentSetupBL insertstatus)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("exec getMax Spl_Status_Id,Student_SetUp_Programs_Default_Special_Education_Status");
                String insertfieldstatus = "insertspecialfieldstatus " + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + insertstatus.FieldId + ",'" + insertstatus.FieldStatus + "'," + insertstatus.SchoolId + ",'" + insertstatus.FieldView + "'";
                DataConnection.ConnectionPath.ExecuteCommand(insertfieldstatus);
            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('Spl_Status_Id','Student_SetUp_Programs_Default_Special_Education_Status')");
                String insertfieldstatus = "Call insertspecialfieldstatus (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + insertstatus.FieldId + ",'" + insertstatus.FieldStatus + "'," + insertstatus.SchoolId + ",'" + insertstatus.FieldView + "')";
                DataConnection.ConnectionPath.ExecuteCommand(insertfieldstatus);
            }
        }

        public int CheckGiftedFieldFromStatusTable(StudentSetupBL fieldcount)
        {
            if (server == "MSSQLServer")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("checkgiftedfieldfromstatustable " + fieldcount.FieldId + "," + fieldcount.SchoolId + "").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            if (server == "MySQL")
            {
                object count;
                count = DataConnection.ConnectionPath.Getdata("Call checkgiftedfieldfromstatustable (" + fieldcount.FieldId + "," + fieldcount.SchoolId + ")").Tables[0].Rows[0][0];
                return Convert.ToInt32(count);
            }
            else
            {
                return 0;
            }
        }

        public void UpdateDefaultGiftedFieldView(StudentSetupBL fieldstatus)
        {
            if (server == "MSSQLServer")
            {
                String updatestatus = "updategifteddefaultfieldview '" + fieldstatus.FieldView + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + "";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
            if (server == "MySQL")
            {
                String updatestatus = "Call updategifteddefaultfieldview ('" + fieldstatus.FieldView + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + ")";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
        }

        public void UpdateDefaultgiftedFieldViewstatus(StudentSetupBL fieldstatus)
        {
            if (server == "MSSQLServer")
            {
                String updatestatus = "updategifteddefaultfieldviewstatus '" + fieldstatus.FieldStatus + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + "";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
            if (server == "MySQL")
            {
                String updatestatus = "Call updategifteddefaultfieldviewstatus ('" + fieldstatus.FieldStatus + "'," + fieldstatus.FieldId + "," + fieldstatus.SchoolId + ")";
                DataConnection.ConnectionPath.ExecuteCommand(updatestatus);
            }
        }

        public void insertgiftedfieldstatus(StudentSetupBL insertstatus)
        {
            if (server == "MSSQLServer")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("exec getMax Gft_Status_id,Student_Setup_Programs_Gifted_Default_Field_Status");
                String insertfieldstatus = "insertgiftedfieldstatus " + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + insertstatus.FieldId + ",'" + insertstatus.FieldStatus + "'," + insertstatus.SchoolId + ",'" + insertstatus.FieldView + "'";
                DataConnection.ConnectionPath.ExecuteCommand(insertfieldstatus);
            }
            if (server == "MySQL")
            {
                DataSet ds = null;
                ds = DataConnection.ConnectionPath.Getdata("Call getMax ('Spl_Status_Id','Student_SetUp_Programs_Default_Special_Education_Status')");
                String insertfieldstatus = "Call insertgiftedfieldstatus (" + Convert.ToInt32(ds.Tables[0].Rows[0][0]) + "," + insertstatus.FieldId + ",'" + insertstatus.FieldStatus + "'," + insertstatus.SchoolId + ",'" + insertstatus.FieldView + "')";
                DataConnection.ConnectionPath.ExecuteCommand(insertfieldstatus);
            }
        }

        public void UpdateCategoryAndOrder(StudentSetupBL SSBL)
        {
            string UpdateCatAndOrd = "";
            DataTable dtCatID = null;
            DataTable dtSortOrd = null;

            if (server == "MSSQLServer")
            {
                dtCatID = DataConnection.ConnectionPath.Getdata("select Cat_Id from Student_Setup_Category where Short_Order =" + SSBL.PreviousOrder).Tables[0];
                if (dtCatID.Rows.Count != 0)
                {
                    dtSortOrd = DataConnection.ConnectionPath.Getdata("select Short_Order from Student_Setup_Category where Cat_Id=" + SSBL.CategoryId).Tables[0];
                    UpdateCatAndOrd = "Update Student_Setup_Category set Short_Order ='" + dtSortOrd.Rows[0][0] + "' where Cat_Id=" + dtCatID.Rows[0][0];
                    DataConnection.ConnectionPath.ExecuteCommand(UpdateCatAndOrd);
                    UpdateCatAndOrd = "Update Student_Setup_Category set Cat_Name ='" + SSBL.CategoryName + "',Short_Order ='" + SSBL.PreviousOrder + "' where Cat_Id=" + SSBL.CategoryId;
                    DataConnection.ConnectionPath.ExecuteCommand(UpdateCatAndOrd);
                }
                else
                {                 
                    UpdateCatAndOrd = "Update Student_Setup_Category set Cat_Name ='" + SSBL.CategoryName + "',Short_Order ='" + SSBL.SortOrder + "' where Cat_Id=" + SSBL.CategoryId;
                    DataConnection.ConnectionPath.ExecuteCommand(UpdateCatAndOrd);
                }
            }
            if (server == "MySQL")
            {

                dtCatID = DataConnection.ConnectionPath.Getdata("select Cat_Id from Student_Setup_Category where Short_Order=Short_Order = N'" + SSBL.SortOrder + "'").Tables[0];
                if (dtCatID.Rows.Count != 0)
                {
                    dtSortOrd = DataConnection.ConnectionPath.Getdata("select Short_Order from Student_Setup_Category where Cat_Id = N'" + SSBL.CategoryId + "'").Tables[0];
                    UpdateCatAndOrd = "Update Student_Setup_Category set Short_Order = N'" + dtSortOrd.Rows[0][0] + "' where Cat_Id = N" + dtCatID.Rows[0][0];
                    DataConnection.ConnectionPath.ExecuteCommand(UpdateCatAndOrd);

                    UpdateCatAndOrd = "Update Student_Setup_Category set Cat_Name = N'" + SSBL.CategoryName + "',Short_Order = N'" + SSBL.SortOrder + "' where Cat_Id=" + SSBL.CategoryId;
                    DataConnection.ConnectionPath.ExecuteCommand(UpdateCatAndOrd);
                }
                else
                {                  
                    UpdateCatAndOrd = "Update Student_Setup_Category set Cat_Name = N'" + SSBL.CategoryName + "',Short_Order = N'" + SSBL.SortOrder + "' where Cat_Id=" + SSBL.CategoryId;
                    DataConnection.ConnectionPath.ExecuteCommand(UpdateCatAndOrd);
                }
            }

        }
    }
}